/******************************************************************************
File: OrgBO.javaneng 

Version 1.0
Date            Author                Changes
Jan.30,2004     PO tools              Created

Copyright (c) 2003, Vanda Group.
all rights reserved.
******************************************************************************/
package com.vandagroup.portal.org;

import java.sql.SQLException;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;

import com.vandagroup.common.bean.BaseBean;
import com.vandagroup.common.bo.ESSBO;
import com.vandagroup.common.bo.UserContext;
import com.vandagroup.common.po.ESSTransaction;
import com.vandagroup.common.util.Paras;
import com.vandagroup.common.util.Result;
import com.vandagroup.common.util.SQLFormat;
import com.vandagroup.common.util.SQLUtility;
import com.vandagroup.common.util.seqgener.KeyGen;
import com.vandagroup.portal.PortalParas;
import com.vandagroup.portal.func.FuncPO;

/**
* �K��Org�K���K���K�ҵ��K���K�1�7
* 
* @see ess.base.bo.ESSBO
*/
public class OrgBO extends ESSBO {

	/**
	 * �K���K��ָ�K���K��Org�K���K��
	 * 
	 * @param bean
	 * @return int
	 * @throws SQLException
	 */
	public int add(OrgFormBean bean) throws SQLException, Exception {

		if (bean.getFuncID() == null) {
			throw new Exception("�K���x�K��һ�K���K�ܺ�");
		}

		OrgPO po = new OrgPO();
		String orgID = KeyGen.getKey("ORG", 8);
		bean.setOrgID(orgID);
		po.setOrgForm(bean);

		ESSTransaction trans = new ESSTransaction();
		trans.begin();
		try {
			int rows = trans.add(po);

			for (int i = 0; i < bean.getFuncID().length; i++) {
				rows += trans
						.executeUpdate("INSERT INTO ORGFUNC (ORGID, FUNCID) VALUES ('"
								+ bean.getOrgID()
								+ "', '"
								+ bean.getFuncID()[i] + "')");

			}

			// �K���K�e��ݠ�K�U�1�7
			//			RolePO rolePO = new RolePO();
			//			RoleFormBean roleFormBean = new RoleFormBean();
			//
			//			String roleID = KeyGen.getKey("ROLE", 8);
			//
			//			roleFormBean.setRoleID(roleID);
			//			roleFormBean.setRoleName("$$$$Ĭ�K�^���K���K�U�1�7$$$$");
			//			roleFormBean.setUserLinkNum(new Integer(0));
			//			roleFormBean.setTotalLinkNum(new Integer(0));
			//			roleFormBean.setLanguage("1");
			//			roleFormBean.setLinkTime(new Integer(0));
			//			roleFormBean.setFace("");
			//			roleFormBean.setSysType(PortalParas.SYS_TYPE_PORTAL);
			//			rolePO.setRoleForm(roleFormBean);
			//
			//			trans.add(rolePO);

//			// �K���K�e��ϲ�K���K�Ԅ1�7
//			OperPO operPO = new OperPO();
//			OperFormBean operFormBean = new OperFormBean();
//
//			String operID = KeyGen.getKey("OPER", 8);
//
//			operFormBean.setOperID(operID);
//			operFormBean.setLoginID("admin" + orgID);
//			operFormBean.setPassword("8A7FF91859371FA9");// �K��ռ�h�K�e���K�1�7
//			operFormBean.setOperName("Ĭ�K�^���K���K���K�Ԅ1�7");
//			operFormBean.setOperType(SysPara.OPRTYPE_WORKER);
//			operFormBean.setOrgID(orgID);
//			operFormBean.setChannel("1");
//			operFormBean.setLanguage("1");
//			operFormBean.setSysType(PortalParas.SYS_TYPE_PORTAL);
//			operFormBean.setStatus(PortalParas.STATUS_VALID);
//
//			operPO.setOperForm(operFormBean);
//
//			trans.add(operPO);
//
//			// �K���K�e��ϲ�K���K�Ա��K�e��ݠ�K�U���K�Ӧ��K�^�1�7
//			trans
//					.executeUpdate("INSERT INTO OPERROLE (ROLEID, OPERID) VALUES ('"
//							+ roleID + "', '" + operID + "')");
//
//			// �K���x�K���K�e��ݠ�K�U���K�Ӧ��K�^�1�7
//			trans
//					.executeUpdate("INSERT INTO ORGROLE (ROLEID, ORGID, STATUS, TYPE) VALUES ('"
//							+ roleID
//							+ "', '"
//							+ orgID
//							+ "', '"
//							+ PortalParas.STATUS_VALID
//							+ "', '"
//							+ PortalParas.CREATE_BY_SELF + "')");

			// �K���x�K��ɫ���K��
//			if (bean.getFuncID() != null && bean.getFuncID().length != 0)
//				trans
//						.executeUpdate("INSERT INTO ORGROLEFUNC (ROLEID, FUNCID, ORGID) SELECT '"
//								+ roleID
//								+ "', FUNCID, '"
//								+ orgID
//								+ "' FROM FUNC WHERE FUNCID IN ("
//								+ SQLFormat.joinAsList(bean.getFuncID(), true)
//								+ ")");
//			if (bean.getRoleID() != null) {
//				for (int i = 0; i < bean.getRoleID().length; i++)
//					trans
//							.executeUpdate("INSERT INTO ORGROLE ( ORGID, ROLEID, STATUS, TYPE ) VALUES( '"
//									+ bean.getOrgID()
//									+ "', '"
//									+ bean.getRoleID()[i]
//									+ "', '"
//									+ PortalParas.STATUS_VALID
//									+ "', '"
//									+ PortalParas.CREATE_BY_UP + "' )");
//			}
		} catch (Exception e) {
			trans.rollback();
			throw e;
		}
		trans.commit();
		return 1;
	}

	/**
	 * �K��ɔ�K���K��ȡ�K��ⷿ��K�q�Ŀ��K�C��ɫ
	 * 
	 * @param user
	 * @return HashMap
	 * @throws SQLException
	 * @throws Exception
	 *             Create Date : 2004-2-11
	 */
	public HashMap getRoles(UserContext user) throws SQLException, Exception {

		OrgPO po = new OrgPO();
		HashMap roles = new HashMap();
		String sql = "SELECT ROLEID, ROLENAME FROM ROLE WHERE ROLEID IN ( SELECT ROLEID FROM ORGROLE WHERE ORGID = '"
				+ user.getOrgID()
				+ "' AND STATUS = '"
				+ PortalParas.STATUS_VALID
				+ "' ) AND ROLENAME NOT LIKE '$$$$%$$$$'";
		BaseBean[] beans = po.query(sql);

		for (int i = 0; beans != null && beans.length != 0 && i < beans.length; i++) {
			roles.put(beans[i].get("ROLEID"), beans[i].get("ROLENAME"));
		}
		return roles;
	}

	/**
	 * �h�K��ָ�K���K��Org�K���K��
	 * 
	 * @param bean
	 * @return int
	 * @throws SQLException
	 */
	public int remove(OrgFormBean bean) throws SQLException, Exception {
		int _rtn = -1;
		ESSTransaction trans = new ESSTransaction();
		trans.begin();
		//�h��C�����ܱ�
		_rtn = trans.executeUpdate("DELETE FROM ORGFUNC WHERE ORGID  = '"
				+ bean.getOrgID() + "'");
		//		�h��C����ɫ���ܱ�
		_rtn = trans.executeUpdate("DELETE FROM ORGROLEFUNC WHERE ORGID  = '"
				+ bean.getOrgID() + "'");
		//		�h��C����ɫ��
		_rtn = trans.executeUpdate("DELETE FROM ORGROLE WHERE ORGID  = '"
				+ bean.getOrgID() + "'");
		//		�h�����T��
		_rtn = trans.executeUpdate("DELETE FROM OPER WHERE ORGID  = '"
				+ bean.getOrgID() + "'");
		//		�h��C����
		_rtn = trans.executeUpdate("DELETE FROM ORG WHERE ORGID = '"
				+ bean.getOrgID() + "'");
		trans.commit();
		return 1;
	}

	/**
	 * �K���K��ָ�K���K��Org�K���K��
	 * 
	 * @param bean
	 * @return int
	 * @throws SQLException
	 */
	public int update(OrgFormBean bean, UserContext user) throws SQLException,
			Exception {
		OrgPO po = new OrgPO();

		/*
		BaseBean[] baseBean = (BaseBean[]) po
				.query("WITH RPL ( ORGID ) AS ( SELECT ROOT.ORGID FROM ORG ROOT WHERE ROOT.UPORGID = '"
						+ bean.getOrgID()
						+ "' UNION ALL SELECT CHILD.ORGID FROM RPL PARENT, ORG CHILD WHERE PARENT.ORGID = CHILD.UPORGID ) SELECT DISTINCT ORGID FROM RPL ORDER BY ORGID");
		String[] orgs = new String[baseBean.length];
		for (int i = 0; i < baseBean.length; i++) {
			orgs[i] = (String) baseBean[i].get("ORGID");
		}
        */
		
		List orgsList = this.getBranchOrgIds(bean.getOrgID(), po) ;
		String[] orgs = (String[])orgsList.toArray(new String[orgsList.size()]) ;
		po.setOrgForm(bean);

		ESSTransaction trans = new ESSTransaction();
		trans.begin();

		po.setWhereClause("ORGID = '" + bean.getOrgID() + "'");
		int rows = trans.update(po);
		if (!bean.getOrgID().equals(user.getOrgID())) {
			rows += trans
					.executeUpdate("DELETE FROM ORGROLEFUNC WHERE ( ORGID = '"
							+ bean.getOrgID()
							+ "' OR ORGID IN ("
							+ (orgs.length != 0 ? SQLFormat.joinAsList(orgs,
									true) : "''")
							+ ") ) AND FUNCID NOT IN ("
							+ (bean.getFuncID().length != 0 ? SQLFormat
									.joinAsList(bean.getFuncID(), true) : "''")
							+ ")");
			System.out.println("DELETE FROM ORGROLEFUNC WHERE ( ORGID = '"
					+ bean.getOrgID()
					+ "' OR ORGID IN ("
					+ (orgs.length != 0 ? SQLFormat.joinAsList(orgs, true)
							: "''")
					+ ") ) AND FUNCID NOT IN ("
					+ (bean.getFuncID().length != 0 ? SQLFormat.joinAsList(bean
							.getFuncID(), true) : "''") + ")");
			rows += trans.executeUpdate("DELETE FROM ORGFUNC WHERE ORGID IN ("
					+ (orgs.length != 0 ? SQLFormat.joinAsList(orgs, true)
							: "''")
					+ ") AND FUNCID NOT IN ("
					+ (bean.getFuncID().length != 0 ? SQLFormat.joinAsList(bean
							.getFuncID(), true) : "''") + ")");
			rows += trans.executeUpdate("DELETE FROM ORGFUNC WHERE ORGID = '"
					+ bean.getOrgID() + "'");

			for (int i = 0; i < bean.getFuncID().length; i++) {
				rows += trans
						.executeUpdate("INSERT INTO ORGFUNC (ORGID, FUNCID) VALUES ('"
								+ bean.getOrgID()
								+ "', '"
								+ bean.getFuncID()[i] + "')");

			}
		}

		trans.commit();

		return rows;
	}

	/**
	 * �K��ȡָ�K���K���K���K�Org�K���K��
	 * 
	 * @param bean
	 * @param pageNO
	 * @return Result
	 * @throws SQLException
	 */
	public Result retrieve(OrgFormBean bean, int pageNO) throws SQLException {
		OrgPO po = new OrgPO();
		po.setWhereClause("");
		po.setResultPage(Result.DEFAULT_PAGE_SIZE, pageNO);
		Result result = po.retrieveResult();
		return result;
	}

	/**
	 * �K��ȡָ�K���K���K��ĵ�K���K�Org�K���K��
	 * 
	 * @param bean
	 * @return OrgFormBean
	 * @throws SQLException
	 */
	public OrgFormBean retrieve(OrgFormBean bean) throws SQLException {
		OrgPO po = new OrgPO();
		po.setWhereClause("ORGID = '" + bean.getOrgID() + "'");
		return ((OrgFormBean) po.retrieveForm());
	}

	/**
	 * �K��ɔ�K���K��ȡ�K��ⷿ��K�Ӧ�Ļ�K��K�1�7
	 * 
	 * @param orgID
	 * @return String[]
	 * @throws SQLException
	 * @throws Exception
	 *             Create Date : 2004-2-9
	 */
	public String[] retrieveFuncs(String orgID) throws SQLException, Exception {
		BaseBean[] baseBean = (BaseBean[]) new OrgPO()
				.query("SELECT FUNCID FROM ORGFUNC WHERE ORGID = '" + orgID
						+ "'");
		String[] funcs = new String[baseBean.length];
		for (int i = 0; i < baseBean.length; i++) {
			funcs[i] = (String) baseBean[i].get("FUNCID");
		}

		return funcs;
	}

	/**
	 * �K��ɔ�K���K�]���K���K��ⷿ��K���K�1�7
	 * 
	 * @param orgID
	 * @return �K���K���K�1�7
	 */
	public String getOrgName(String orgID) throws Exception {
		if (orgID == null || orgID.trim().equals("")) {
			throw (new Exception("�K���K���K���K�Є1�7"));
		}

		OrgPO po = new OrgPO();
		po.setWhereClause("ORGID = '" + orgID + "'");
		OrgFormBean bean = (OrgFormBean) po.retrieveForm();
		return (bean.getOrgName());
	}

	/**
	 * �K��ȡ�K���K��
	 * 
	 * @param orgID
	 * @return
	 * @throws Exception
	 */
	public String getAreaID(String orgID) throws Exception {
		if (orgID == null || orgID.trim().equals("")) {
			throw (new Exception("�K���K���K���K�Є1�7"));
		}

		OrgPO po = new OrgPO();
		po.setWhereClause("ORGID = '" + orgID + "'");
		OrgFormBean bean = (OrgFormBean) po.retrieveForm();
		return (bean.getAreaID());
	}

	/**
	 * �K���K�orgIDȡ�K���K���K���K��ⷿ��K���K���K�U���K���K��sql�K���K��in�K�Ӿ�
	 * 
	 * @param orgID
	 * @return String
	 * @throws Exception
	 *             Create Date : 2004-2-25
	 */
	public String getChildOrgIDInClause(String orgID) throws Exception {
		/*
		String rtn = "";
		String[] orgs = null;

		OrgPO po = new OrgPO();
		BaseBean[] baseBean = (BaseBean[]) po
				.query("WITH RPL ( ORGID ) AS ( SELECT ROOT.ORGID FROM ORG ROOT WHERE ROOT.UPORGID = '"
						+ orgID
						+ "' UNION ALL SELECT CHILD.ORGID FROM RPL PARENT, ORG CHILD WHERE PARENT.ORGID = CHILD.UPORGID ) SELECT DISTINCT ORGID FROM RPL ORDER BY ORGID");
		if (baseBean == null || baseBean.length == 0) {
			orgs = new String[1];
			orgs[0] = orgID;
		} else {
			orgs = new String[baseBean.length + 1];
			orgs[0] = orgID;
			for (int i = 0; i < baseBean.length; i++) {
				orgs[i + 1] = (String) baseBean[i].get("ORGID");
			}
		}
		return SQLFormat.joinAsList(orgs, true);
		*/
		
		List orgIdList = getBranchOrgIds(orgID,new OrgPO()) ;
		orgIdList.add(orgID) ;
		return SQLFormat.joinAsList((String [])orgIdList.toArray(new String[orgIdList.size()]), true) ;
	}

	private List getBranchOrgIds(String rootId,OrgPO po) throws Exception {

		List result = new LinkedList() ;
		
		BaseBean[] baseBean = (BaseBean[]) po.query("SELECT ORGID  FROM ORG WHERE UPORGID ='"+rootId+"'");

		for (int i = 0; i < baseBean.length; i++) {
			String funcId = (String) baseBean[i].get("ORGID");
			result.add(funcId) ;
			
			result.addAll(getBranchOrgIds(funcId, po)) ;

		}
		
		return result ;
	}

	public HashMap getOrgList() throws SQLException, Exception {
		HashMap orgList = new HashMap();
		OrgPO po = new OrgPO();
		BaseBean[] beans = po
				.query("SELECT ORGID,ORGNAME FROM ORG WHERE ORGID<>'00000000' AND STATUS <> '"
						+ PortalParas.STATUS_DELETE + "'");
		for (int i = 0; beans != null && beans.length != 0 && i < beans.length; i++) {
			orgList.put(beans[i].get("ORGID"), beans[i].get("ORGNAME"));
		}
		return orgList;
	}

	public HashMap getBankOrgList() throws SQLException, Exception {
		HashMap orgList = new HashMap();
		OrgPO po = new OrgPO();
		BaseBean[] beans = po
				.query("SELECT ORGID,ORGNAME FROM ORG WHERE ORGID<>'00000000' AND ORGID<>'"
						+ Paras.get("EBankOrgID")
						+ "' AND UPORGID<>'"
						+ Paras.get("EBankOrgID")
						+ "' AND STATUS <> '"
						+ PortalParas.STATUS_DELETE + "'");
		for (int i = 0; beans != null && beans.length != 0 && i < beans.length; i++) {
			orgList.put(beans[i].get("ORGID"), beans[i].get("ORGNAME"));
		}
		return orgList;
	}

	/**
	 * �K��ȡָ�K���K���K���K�Org�K���K��
	 * 
	 * @param bean
	 * @param pageNO
	 * @return Result
	 * @throws SQLException
	 */
	public Result retrieveValidOrg(OrgFormBean bean, int pageNO)
			throws SQLException, Exception {
		OrgPO po = new OrgPO();

		String whereClause = "";
		whereClause = SQLUtility.addLikeClause(whereClause, "orgID", bean
				.getOrgID());
		whereClause = SQLUtility.addWhereClause(whereClause, "STATUS = '"
				+ PortalParas.STATUS_VALID + "'");

		po.setWhereClause(whereClause);
		po.setResultPage(Result.DEFAULT_PAGE_SIZE, pageNO);

		Result result = po.queryResult();
		BaseBean[] beans = result.getBaseBeans();
		return result;
	}
}
